from typing import Optional, List

from sqlalchemy import BigInteger, Boolean, Column, Date, DateTime, Index, Integer, Numeric, PrimaryKeyConstraint, \
    Sequence, SmallInteger, String, Table, Text, UniqueConstraint, text, ForeignKey
from sqlalchemy.dialects.postgresql import TIMESTAMP
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Relationship, relationship, declarative_base
import datetime
import decimal

# 创建基类
Base = declarative_base()


# 用户和角色关联表 - 首先定义关联表，避免依赖问题
class AmSysUserRole(Base):
    __tablename__ = 'am_sys_user_role'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_user_role_pkey'),
        {'comment': '后台用户和角色关系表', 'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    # user_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey('am_sys_user.id'))
    # role_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("am_sys_role.id"))
    user_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    role_id: Mapped[Optional[int]] = mapped_column(BigInteger)


# 角色和权限关联表
class AmSysRolePermission(Base):
    __tablename__ = 'am_sys_role_permission'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_role_permission_pkey'),
        {'comment': '后台用户角色和权限关系表', 'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    # role_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("am_sys_role.id"))
    # permission_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("am_sys_permission.id"))
    role_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    permission_id: Mapped[Optional[int]] = mapped_column(BigInteger)


# 权限表
class AmSysPermission(Base):
    __tablename__ = 'am_sys_permission'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_permission_pkey'),
        {'comment': '后台用户权限表', 'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    pid: Mapped[Optional[int]] = mapped_column(BigInteger, comment='父级权限id')
    name: Mapped[Optional[str]] = mapped_column(String(100), comment='名称')
    value: Mapped[Optional[str]] = mapped_column(String(200), comment='权限值')
    icon: Mapped[Optional[str]] = mapped_column(Text, comment='图标')
    type: Mapped[Optional[int]] = mapped_column(Integer, comment='权限类型：0->目录；1->菜单；2->按钮（接口绑定权限）')
    uri: Mapped[Optional[str]] = mapped_column(String(200), comment='前端资源路径')
    status: Mapped[Optional[int]] = mapped_column(Integer, comment='启用状态；0->禁用；1->启用')
    create_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6), comment='创建时间')
    sort_seq: Mapped[Optional[int]] = mapped_column(Integer, comment='排序')
    method: Mapped[Optional[str]] = mapped_column(String(10))
    create_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    update_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6))
    update_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    fname: Mapped[Optional[str]] = mapped_column(String(100), comment='繁体名称')
    yname: Mapped[Optional[str]] = mapped_column(String(100), comment='英文名称')
    systype: Mapped[Optional[str]] = mapped_column(String(20), comment='菜单所属系统 S 食品安全 I 物联网  L 实验室')

    # roles: Mapped[List["AmSysRole"]] = relationship(
    #     "AmSysRole",
    #     secondary=AmSysRolePermission.__table__,
    #     back_populates="permissions"
    # )


# 角色表
class AmSysRole(Base):
    __tablename__ = 'am_sys_role'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_role_pkey'),
        {'comment': '后台用户角色表', 'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    name: Mapped[Optional[str]] = mapped_column(String(100), comment='名称')
    description: Mapped[Optional[str]] = mapped_column(String(500), comment='描述')
    user_count: Mapped[Optional[int]] = mapped_column(Integer, comment='后台用户数量')
    create_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6), comment='创建时间')
    status: Mapped[Optional[int]] = mapped_column(Integer, comment='启用状态：0->禁用；1->启用')
    sort_seq: Mapped[Optional[int]] = mapped_column(Integer)
    create_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    update_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6))
    update_id: Mapped[Optional[int]] = mapped_column(BigInteger)

    # users: Mapped[List["AmSysUser"]] = relationship(
    #     "AmSysUser",
    #     secondary=AmSysUserRole.__table__,
    #     back_populates="roles"
    # )

    # permissions: Mapped[List["AmSysPermission"]] = relationship(
    #     "AmSysPermission",
    #     secondary=AmSysRolePermission.__table__,
    #     back_populates="roles"
    # )


# 用户表 - 最后定义用户表，确保所有依赖类都已定义
class AmSysUser(Base):
    __tablename__ = 'am_sys_user'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_user_pkey'),
        Index('idx_user_phone', 'phone'),
        Index('idx_user_username', 'username'),
        {'comment': '后台用户表', 'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    username: Mapped[Optional[str]] = mapped_column(String(64), comment='用户名(登录用)')
    password: Mapped[Optional[str]] = mapped_column(String(64), comment='密码')
    icon: Mapped[Optional[str]] = mapped_column(String(500), comment='头像')
    email: Mapped[Optional[str]] = mapped_column(String(100), comment='邮箱')
    nick_name: Mapped[Optional[str]] = mapped_column(String(200), comment='昵称')
    note: Mapped[Optional[str]] = mapped_column(String(500), comment='备注信息')
    create_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6), comment='创建时间')
    login_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6), comment='最后登录时间')
    status: Mapped[Optional[int]] = mapped_column(Integer, comment='帐号启用状态：0->禁用；1->启用')
    phone: Mapped[Optional[str]] = mapped_column(String(20), comment='手机号(登录用)')
    create_id: Mapped[Optional[int]] = mapped_column(BigInteger, comment='创建用户')
    update_time: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP(precision=6), comment='更新时间')
    update_id: Mapped[Optional[int]] = mapped_column(BigInteger, comment='更新用户')
    fsite_no_belong: Mapped[Optional[str]] = mapped_column(String(20), comment='所属地区')
    ftype: Mapped[Optional[int]] = mapped_column(Integer, comment='用户类型(2前端，1后端，3都是)')
    fpoint_noes: Mapped[Optional[str]] = mapped_column(String(255), comment='教学楼编号')
    fcard: Mapped[Optional[str]] = mapped_column(String(20), comment='用户卡号')
    password_clear: Mapped[Optional[str]] = mapped_column(String(64), comment='明文密码')
    fappno: Mapped[Optional[str]] = mapped_column(String(64), comment='平板编号')
    enterprise_wechat: Mapped[Optional[str]] = mapped_column(String(100), comment='企业微信')

    # roles: Mapped[List["AmSysRole"]] = relationship(
    #     "AmSysRole",
    #     secondary=AmSysUserRole.__table__,
    #     back_populates="users"
    # )


class AmSysUserDetail(Base):
    __tablename__ = 'am_sys_user_detail'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_user_detail_pkey'),
        UniqueConstraint('user_id', name='user_id_only'),
        {'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    user_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    enterprise_wechat: Mapped[Optional[str]] = mapped_column(String(255), comment='企业微信用户名')


class AmSysUserPermission(Base):
    __tablename__ = 'am_sys_user_permission'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='am_sys_user_permission_pkey'),
        {'comment': '后台用户和权限关系表(除角色中定义的权限以外的加减权限)', 'schema': 'public'}
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    user_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    permission_id: Mapped[Optional[int]] = mapped_column(BigInteger)
    type: Mapped[Optional[int]] = mapped_column(Integer)


class AmSysUserPoint(Base):
    __tablename__ = 'am_sys_user_point'
    __table_args__ = (
        PrimaryKeyConstraint('fno', name='am_sys_user_point_pkey'),
        {'schema': 'public'}
    )

    fno: Mapped[int] = mapped_column(Integer, primary_key=True, comment='自增id')
    fid: Mapped[int] = mapped_column(BigInteger, comment='用户id')
    fpoint_no: Mapped[str] = mapped_column(String(20), comment='教室编号')


class ATsSite(Base):
    __tablename__ = 'a_ts_site'
    __table_args__ = (
        PrimaryKeyConstraint('fsite_no', name='a_ts_site_pkey'),
        {'comment': '地区资料', 'schema': 'public'}
    )

    fsite_no: Mapped[str] = mapped_column(String(20), primary_key=True, comment='地区编号')
    fsite_name: Mapped[str] = mapped_column(String(48), comment='地区名称')
    fsite_parent: Mapped[str] = mapped_column(String(20), comment='上级地区')
    fsite_level: Mapped[int] = mapped_column(Integer, comment='地区编号(0:省,1:市;2:区,3:学校)ts_dict.1001')
    fsite_state: Mapped[int] = mapped_column(Integer, comment='状态(1正常/0停用)')
    fsite_l: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(20, 10), comment='纬度')
    fsite_d: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(20, 10), comment='经度')
    fengineer: Mapped[Optional[str]] = mapped_column(String(20), comment='工程师')
    fphone: Mapped[Optional[str]] = mapped_column(String(15), comment='手机')
    ftelno: Mapped[Optional[str]] = mapped_column(String(20), comment='电话')
    femail: Mapped[Optional[str]] = mapped_column(String(50), comment='邮箱')
    fimage: Mapped[Optional[str]] = mapped_column(String(100), comment='平面图')
    fupddate: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime, comment='修改时间')
    foperator: Mapped[Optional[str]] = mapped_column(String(20), comment='操作员')
    fso2: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前二氧化硫值')
    fno2: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前二氧化氮值')
    fco: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前一氧化碳值')
    fo3: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前臭氧值')
    fpm25: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前PM2.5值')
    fpm10: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前PM10值')
    fwd: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室外温度')
    fsd: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室外温度')
    noise: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 0), comment='噪音')
    faqi: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='当前AQI值')
    faqi_tb: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 2), comment='同比AQI值')
    faqi_hb: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 2), comment='环比AQI值')
    fcontaminants: Mapped[Optional[str]] = mapped_column(String(80), comment='首要污染物')
    fpm26: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内PM2.5值')
    fpm11: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内PM10值')
    fco2: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内二氧化碳(CO2)')
    fjq: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内甲醛')
    fyjw: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内挥发性有机物')
    fwd2: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内温度')
    fsd2: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 3), comment='室内湿度')
    faqi2: Mapped[Optional[str]] = mapped_column(String(20), comment='室内AQI')
    fcontaminants2: Mapped[Optional[str]] = mapped_column(String(80), comment='室内首要污染物')
    fnetwork_ok: Mapped[Optional[int]] = mapped_column(Integer, comment='联网成功数')
    fnetwork_no: Mapped[Optional[int]] = mapped_column(Integer, comment='断网成功数')
    frefresh: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime, comment='刷新时间')
    fsite_type: Mapped[Optional[int]] = mapped_column(Integer, comment='客户行业(教育/零售)')
    day_off: Mapped[Optional[str]] = mapped_column(String(20), comment='休息日(1,2,3,4,5,6,7)')
    fbegtime: Mapped[Optional[str]] = mapped_column(String(5), comment='上班时间')
    fendtime: Mapped[Optional[str]] = mapped_column(String(5), comment='下班时间')
    fimage3d: Mapped[Optional[str]] = mapped_column(String(100), comment='3D图')
    fboxflag: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='柜子授权')
    fuseflag: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='预约授权')
    flink_man: Mapped[Optional[str]] = mapped_column(String(20), comment='联系人')
    fusetype: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='物料预约')
    finvtype: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='盘点预约')
    fouttype: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='出库预约')
    fapiflag: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='启用课程表')
    fcourseflag: Mapped[Optional[int]] = mapped_column(Integer, server_default=text('0'), comment='是否开启课程')
    fbegindate: Mapped[Optional[datetime.date]] = mapped_column(Date, comment='授权开始日期')
    fenddate: Mapped[Optional[datetime.date]] = mapped_column(Date, comment='授权结束日期')
    fcharge: Mapped[Optional[int]] = mapped_column(Integer, comment='收费方式(1:按次/2:按小时)s_ts_dict.2019')
    fchargeprice: Mapped[Optional[decimal.Decimal]] = mapped_column(Numeric(12, 2), comment='收费标准')
    fappurl: Mapped[Optional[str]] = mapped_column(String(100), comment='门禁URL')
    fappid: Mapped[Optional[str]] = mapped_column(String(20), comment='门禁APPID')
    fappkey: Mapped[Optional[str]] = mapped_column(String(64), comment='门禁APPKEY')
    fappsecret: Mapped[Optional[str]] = mapped_column(String(64), comment='门禁AppSecret')
